const { sequelize } = require('../models');

async function createJoinTables() {
  try {
    console.log('🚀 Creating join tables...');
    
    // 创建用户关注表
    await sequelize.query(`
      CREATE TABLE IF NOT EXISTS user_followers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        followerId INT NOT NULL,
        followingId INT NOT NULL,
        createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (followerId) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (followingId) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE KEY unique_follower_following (followerId, followingId)
      )
    `);
    
    // 创建用户好友表
    await sequelize.query(`
      CREATE TABLE IF NOT EXISTS user_friends (
        id INT AUTO_INCREMENT PRIMARY KEY,
        userId INT NOT NULL,
        friendId INT NOT NULL,
        createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (friendId) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE KEY unique_user_friend (userId, friendId)
      )
    `);
    
    // 创建活动参与者表
    await sequelize.query(`
      CREATE TABLE IF NOT EXISTS activity_participants (
        id INT AUTO_INCREMENT PRIMARY KEY,
        activityId INT NOT NULL,
        userId INT NOT NULL,
        status ENUM('joined', 'cancelled', 'completed') DEFAULT 'joined',
        notes TEXT,
        joinedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (activityId) REFERENCES activities(id) ON DELETE CASCADE,
        FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE KEY unique_activity_user (activityId, userId)
      )
    `);
    
    // 创建活动点赞表
    await sequelize.query(`
      CREATE TABLE IF NOT EXISTS activity_likes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        activityId INT NOT NULL,
        userId INT NOT NULL,
        likedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (activityId) REFERENCES activities(id) ON DELETE CASCADE,
        FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE KEY unique_activity_user_like (activityId, userId)
      )
    `);
    
    console.log('✅ Join tables created successfully!');
    process.exit(0);
  } catch (error) {
    console.error('❌ Failed to create join tables:', error);
    process.exit(1);
  }
}

createJoinTables();
